﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

using MySql.Data.MySqlClient;
using System.Collections;

/// <summary>
/// MySQL - ASP.NET data processing utility.
/// </summary>
namespace Sql.DataAccessLayer
{
    public sealed class SqlHelper
    {
        private SqlHelper()
        {
        }
        //New

        private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
        {            
            foreach (MySqlParameter p in commandParameters)
            {
                //check for derived output value with no value assigned
                if (p.Direction == ParameterDirection.InputOutput & p.Value == null)
                {
                    p.Value = null;
                }
                command.Parameters.Add(p);
            }
        }
        //AttachParameters

        private static void AssignParameterValues(MySqlParameter[] commandParameters, object[] parameterValues)
        {

            int i = 0;
            int j = 0;

            if ((commandParameters == null) & (parameterValues == null))
            {
                //do nothing if we get no data
                return;
            }

            // we must have the same number of values as we pave parameters to put them in
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            //value array
            j = (int)commandParameters.Length - 1;
            for (i = 0; i <= j; i++)
            {
                commandParameters[i].Value = parameterValues[i];
            }

        }
        //AssignParameterValues

        private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters)
        {

            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            //associate the connection with the command
            command.Connection = connection;

            //set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            //if we were provided a transaction, assign it.
            if ((transaction != null))
            {
                command.Transaction = transaction;
            }

            //set the command type
            command.CommandType = commandType;

            //attach the command parameters if they are provided
            if ((commandParameters != null))
            {
                AttachParameters(command, commandParameters);
            }

            return;
        }
        //PrepareCommand


        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            //create & open a MySqlConnection, and dispose of it after we are done.
            MySqlConnection cn = new MySqlConnection(connectionString);
            try
            {
                if (cn.State == ConnectionState.Closed) cn.Open();

                //call the overload that takes a connection in place of the connection string
                return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
        }
        //ExecuteNonQuery

        public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
        {
            MySqlParameter[] commandParameters = null;

            //if we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) & parameterValues.Length > 0)
            {
                //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)

                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                //assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                //call the overload that takes an array of SqlParameters
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            //otherwise we can just call the SP without params
            else
            {
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }
        //ExecuteNonQuery

        public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {

            //create a command and prepare it for execution
            MySqlCommand cmd = new MySqlCommand();
            int retval = 0;

            PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);

            //finally, execute the command.
            retval = cmd.ExecuteNonQuery();

            //detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            return retval;

        }
        //ExecuteNonQuery

        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            //create & open a MySqlConnection, and dispose of it after we are done.
            MySqlConnection cn = new MySqlConnection(connectionString);
            try
            {
                if (cn.State == ConnectionState.Closed) cn.Open();

                //call the overload that takes a connection in place of the connection string
                return ExecuteDataset(cn, commandType, commandText, commandParameters);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
        }
        //ExecuteDataset

        public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
        {

            MySqlParameter[] commandParameters = null;

            //if we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) & parameterValues.Length > 0)
            {
                //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                //assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                //call the overload that takes an array of SqlParameters
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            //otherwise we can just call the SP without params
            else
            {
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }
        //ExecuteDataset

        public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {

            //create a command and prepare it for execution
            MySqlCommand cmd = new MySqlCommand();
            DataSet ds = new DataSet();
            MySqlDataAdapter da = default(MySqlDataAdapter);

            PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);

            //create the DataAdapter & DataSet
            da = new MySqlDataAdapter(cmd);

            //fill the DataSet using default values for DataTable names, etc.
            da.Fill(ds);

            //detach the SqlParameters from the command object, so they can be used again
            //cmd.Parameters.Clear()

            //return the dataset
            return ds;

        }
        //ExecuteDataset

        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            //create & open a MySqlConnection, and dispose of it after we are done.
            MySqlConnection cn = new MySqlConnection(connectionString);
            try
            {
                if (cn.State == ConnectionState.Closed) cn.Open();

                //call the overload that takes a connection in place of the connection string
                return ExecuteScalar(cn, commandType, commandText, commandParameters);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
        }
        //ExecuteScalar

        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            MySqlParameter[] commandParameters = null;

            //if we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) & parameterValues.Length > 0)
            {
                //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                //assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                //call the overload that takes an array of SqlParameters
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            //otherwise we can just call the SP without params
            else
            {
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }
        //ExecuteScalar

        public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            //create a command and prepare it for execution
            MySqlCommand cmd = new MySqlCommand();
            object retval = null;

            PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);

            //execute the command & return the results
            retval = cmd.ExecuteScalar();

            //detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            return retval;

        }


        public static int ExecuteNonQuery_v2(string connectionString, CommandType commandType, string commandText)
        {
            MySqlConnection connection = new MySqlConnection(Config.ConnectionString);
            if (connection.State != ConnectionState.Open) connection.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = connection;
            int retval = 0;
            retval = cmd.ExecuteNonQuery();
            cmd.Dispose();
            connection.Close();
            return retval;
        }

        public static object ExecuteScalar_v2(string connectionString, CommandType commandType, string commandText)
        {
            MySqlConnection connection = new MySqlConnection(Config.ConnectionString);
            if (connection.State != ConnectionState.Open) connection.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = connection;
            object retval = null;
            //execute the command & return the results
            retval = cmd.ExecuteScalar();
            cmd.Dispose();
            connection.Close();
            return retval;
        }
    }
    //ExecuteScalar
    //SqlHelper

    public sealed class SqlHelperParameterCache
    {

        private SqlHelperParameterCache()
        {
        }
        //New 

        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());


        private static MySqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter, params object[] parameterValues)
        {
            MySqlConnection cn = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(spName, cn);
            MySqlParameter[] discoveredParameters = null;

            try
            {
                if (cn.State == ConnectionState.Closed) cn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                MySqlCommandBuilder.DeriveParameters(cmd);
                if (!includeReturnValueParameter)
                {
                    cmd.Parameters.RemoveAt(0);
                }

                discoveredParameters = new MySqlParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(discoveredParameters, 0);
            }
            finally
            {
                cmd.Dispose();
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }


            return discoveredParameters;

        }
        //DiscoverSpParameterSet


        private static MySqlParameter[] CloneParameters(MySqlParameter[] originalParameters)
        {

            int i = 0;
            int j = (int)originalParameters.Length - 1;
            MySqlParameter[] clonedParameters = new MySqlParameter[j + 1];

            for (i = 0; i <= j; i++)
            {
                 clonedParameters[i] = (MySqlParameter)((ICloneable)originalParameters[i]).Clone();
               
            }

            return clonedParameters;
        }
        //CloneParameters

        public static void CacheParameterSet(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        {
            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }
        //CacheParameterSet

        public static MySqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            string hashKey = connectionString + ":" + commandText;
            MySqlParameter[] cachedParameters = (MySqlParameter[])paramCache[hashKey];

            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }
        //GetCachedParameterSet

        public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }
        //GetSpParameterSet 

        public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {

            MySqlParameter[] cachedParameters = null;
            string hashKey = null;

            hashKey = connectionString + ":" + spName;
            if (includeReturnValueParameter == true)
            {
                hashKey = hashKey + ":include ReturnValue Parameter";
            }

            cachedParameters = (MySqlParameter[])paramCache[hashKey];

            if ((cachedParameters == null))
            {
                paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter);
                cachedParameters = (MySqlParameter[])paramCache[hashKey];
            }


            return CloneParameters(cachedParameters);

        }

     
    }
    


    
}
